﻿if exists (select 1
          from sysobjects
          where  id = object_id('bd_fn_getSettleDate')
          and type in ('IF', 'FN', 'TF'))
   drop function bd_fn_getSettleDate
go


-- =============================================
-- Author:      YFC
-- Create date: 2020-07-23 17:01:44
-- Database:    [scm_main-hysj2]
-- Description: 系统设置-根据日期获取结算年月，返回第一天，如：2020-08-01
-- =============================================

CREATE FUNCTION dbo.bd_fn_getSettleDate
(
  @now DATE
)
RETURNS DATE
BEGIN

  DECLARE @curYear INT
         ,@curMonth INT

  SELECT @curYear = YEAR(@now), @curMonth = MONTH(@now)

  DECLARE @settleDayEnable INT
         ,@settleDayBegin INT
         ,@settleDayEnd INT
 

  SELECT TOP 1 @settleDayEnable = settle_day_enable, @settleDayBegin = settle_day_begin, @settleDayEnd = settle_day_end
  FROM bd_sys_global_params
  --  WHERE dept_id = 203113

  DECLARE @result DATE

  IF ISNULL(@settleDayEnable, 0) = 1 
  BEGIN  
    --结算月开始年月
    DECLARE @settleYearBegin  INT, @settleMonthBegin INT
    SET @settleYearBegin = IIF(@curMonth - 1 < 1, @curYear - 1, @curYear)
    SET @settleMonthBegin = IIF(@curMonth - 1 < 1, 12, @curMonth - 1)
  
    --结算月结束年月
    DECLARE @settleYearEnd INT = YEAR(@now), @settleMonthEnd INT = MONTH(@now)
  
    DECLARE @settleDateBegin DATE
           ,@settleDateEnd DATE
  
    --结算开始日期
    SET @settleDateBegin = CAST(@settleYearBegin AS VARCHAR(4)) + '-' + CAST(@settleMonthBegin AS VARCHAR(2)) + '-' + CAST(@settleDayBegin AS VARCHAR(2))
    --结算结束日期
    SET @settleDateEnd = CAST(@settleYearEnd AS VARCHAR(4)) + '-' + CAST(@settleMonthEnd AS VARCHAR(2)) + '-' + CAST(@settleDayEnd AS VARCHAR(2))
  
  
    DECLARE @settleYear INT = YEAR(@settleDateEnd), @settleMonth INT = MONTH(@settleDateEnd)
  
    IF DATEDIFF(DAY, @now, @settleDateEnd) < 0 BEGIN  
    	--当前日期大于结算结束日期，表示已经到下一个月
      SET @settleMonth = IIF(@settleMonth + 1 > 12, 1, @settleMonth + 1)
      SET @settleYear = IIF(@settleMonth + 1 > 12, @settleYear + 1, @settleYear)
    END  	

      SET @result = CAST(@settleYear AS VARCHAR(4)) + '-' + CAST(@settleMonth AS VARCHAR(2)) + '-1'
  END
  ELSE
  BEGIN
  	SET @result = CAST(@curYear AS VARCHAR(4)) + '-' + CAST(@curMonth AS VARCHAR(2)) + '-1'
  END
  
  RETURN @result
END
GO
